/*
 * user-db.js
 * smart home for amazon echo.
 *
 * Created by cx.
 * Copyright 2019, ttis.
 */

const {
  selectQuery,
  selectArrQuery,
  alterQuery
} = require('./db-helper')

const $sql = {
  update_mesh_info:
      'INSERT INTO mesh_info (meshUUID,meshName,createTime,updateTime) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE meshName = IF ("?"="NULL", meshName, ?),updateTime = ?',
  insert_mesh_maddr_info:
      'INSERT INTO mesh_maddr_info (meshUUID,buildingUUID,maddr,createTime) VALUES (?,?,?,?)',
  update_file:
      'UPDATE mesh_info set profile=?,updateTime=?,version = version+1 WHERE meshUUID=?',
  get_mesh_info:
      'SELECT t1.buildingUUID,t1.buildingName,t1.companyName,t1.address,t1.city,t1.introduction,t1.createTime,t2.isSelected,t4.version,COALESCE(t4.meshUUID, \'\') as meshUUID,COALESCE(t4.meshName, \'\') as meshName,COALESCE(t4.profile, \'\') as profile,COALESCE(t4.image, \'\') as image FROM  building AS t1 LEFT JOIN building_maddr AS t2 ON t1.buildingUUID = t2.buildingUUID and t2.del = 0 LEFT JOIN mesh_maddr_info AS t3 ON t2.buildingUUID = t3.buildingUUID and t3.del = 0 LEFT JOIN mesh_info AS t4 ON t3.meshUUID = t4.meshUUID WHERE t2.maddr= ? ',
  maddr_exist:
      'SELECT * FROM user_info WHERE maddr = ?',
  register_user_info:
      'INSERT INTO user_info (maddr, pwd, name,createTime,updateTime,token) VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE pwd = ?,name = ?,createTime = ?,updateTime = ?,token = ?',
  update_verfiy_code:
      'UPDATE user_info set code=?,updateTime=? WHERE maddr=?',
  verify_auth:
      'SELECT updateTime,code FROM user_info WHERE maddr = ?',
  update_verfiy:
      'UPDATE user_info set verify=?,updateTime=? ,code = ? WHERE maddr=?',
  update_device_info:
      'INSERT INTO device_info (maddr,time,deviceNo,deviceName,platform) VALUES (?, ?,?,?,?) ON DUPLICATE KEY UPDATE time=?, deviceName = ?,platform=?',
  token_exist:
      'SELECT maddr,token FROM user_info WHERE maddr = ?',
  device_exist:
      'SELECT maddr,deviceNo FROM device_info WHERE maddr = ?',
  update_building:
      'INSERT INTO building (buildingUUID,buildingName,companyName,address,city,introduction,createTime,updateTime) VALUES ( ?,?, ?, ?,?,?,?,?) ON DUPLICATE KEY UPDATE buildingName = IF ("?"="NULL", buildingName, ?), companyName = IF ("?"="NULL", companyName, ?), address = IF ("?"="NULL", address, ?), city = IF ("?"="NULL", city, ?), introduction = IF ("?"="NULL", introduction, ?), updateTime=?',
  insert_building_maddr:
      'INSERT INTO building_maddr (maddr,buildingUUID,updateTime) VALUES (?, ?,?)',
  update_building_maddr:
      'UPDATE building_maddr SET isSelected=IF(buildingUUID = ?, 1, 0) WHERE maddr=?',
  select_role:
      'SELECT role FROM building_maddr WHERE maddr = ? AND buildingUUID=? and del = 0',
  update_image:
      'UPDATE mesh_info set image=?,updateTime=?,version = version+1 WHERE meshUUID=?',
  insert_log_profile:
      'INSERT INTO log_profile (maddr,profile,image,meshUUID,time,date) VALUES (?, ?,(SELECT image FROM mesh_info WHERE meshUUID =?),?,?,?)',
  insert_log_image:
      'INSERT INTO log_image (maddr,image,meshUUID,time,date) VALUES (?, ?,?,?,?)',
  select_qrcode_count:
      'SELECT count(*) as count FROM qr_code WHERE time >= NOW() - INTERVAL 24 HOUR AND buildingUUID=?',
  insert_share_qrcode:
      'INSERT INTO qr_code (maddr,buildingUUID,qrcode,role,time) VALUES (?,?, ?,?,?)',
  select_qrcode:
      'SELECT receiver,maddr,buildingUUID,role FROM qr_code WHERE qrcode=? AND time >= NOW() - INTERVAL 24 HOUR',
  update_QR_code:
      'UPDATE qr_code set receiver=?,updateTime=? WHERE qrcode=?',
  insert_building_maddr_scan:
      'INSERT INTO building_maddr (maddr,buildingUUID,role,updateTime) VALUES (?, ?,?,?) ON DUPLICATE KEY UPDATE role=?, updateTime = ?,del=0',
  insert_mesh_maddr_info_scan:
      'insert into mesh_maddr_info (maddr,meshUUID,buildingUUID,createTime) select ?,meshUUID,?, ? FROM mesh_maddr_info where maddr=? AND buildingUUID = ? and del = 0 ON DUPLICATE KEY UPDATE del = 0, createTime = ?',
  get_version:
      'SELECT (SELECT version FROM building WHERE buildingUUID = ? and del = 0 ) as version_b, (SELECT version FROM mesh_info WHERE meshUUID = ?  and del = 0 ) as version',
  update_building_maddr_del_all:
      'UPDATE building_maddr SET del = 1 WHERE maddr=?',
  update_mesh_del_all:
      'UPDATE mesh_maddr_info SET del = 1 WHERE maddr=?',
  update_building_all:
      'UPDATE building_maddr a JOIN building b ON a.buildingUUID = b.buildingUUID SET a.del = 1, b.del = 1 WHERE a.buildingUUID = ?; ',
  update_building_maddr_del:
      'UPDATE building_maddr SET del = 1 WHERE maddr=? AND buildingUUID=?;',
  select_role_mesh:
      'SELECT a.role,a.buildingUUID FROM building_maddr AS a LEFT JOIN mesh_maddr_info b ON a.buildingUUID = b.buildingUUID and a.maddr = b.maddr WHERE b.maddr=? AND b.meshUUID = ?;',
  update_mesh_del:
      'UPDATE mesh_maddr_info SET del = 1 WHERE maddr=? and buildingUUID=?',
  update_mesh:
      'UPDATE mesh_maddr_info SET del = 1 WHERE maddr=? and meshUUID=?',
  update_user_info:
      'UPDATE user_info SET name = ? , updateTime=? WHERE maddr=?',
  update_pwd:
      'UPDATE user_info SET pwd = ? , updateTime=?,token = ?,code=? WHERE maddr=?',
  delete_user:
      'DELETE FROM user_info WHERE maddr=?',
  select_building:
      'SELECT t1.buildingUUID,t1.buildingName,t1.companyName,t1.address,t1.city,t1.introduction,t1.createTime,t1.version,t2.isSelected,t2.role FROM  building AS t1 LEFT JOIN building_maddr AS t2 ON t1.buildingUUID = t2.buildingUUID and t2.del = 0 WHERE t2.maddr= ? ',
  select_mesh:
      'SELECT b.meshUUID,COALESCE(b.meshName,\'\') as meshName,COALESCE(b.profile,\'\') as profile,b.version,COALESCE(b.image, \'\') as image FROM mesh_info b LEFT JOIN mesh_maddr_info a ON a.meshUUID = b.meshUUID where a.maddr = ? and a.buildingUUID = ? AND a.del = 0 AND ? IS NULL OR b.meshUUID = ?;',
  update_building_version:
      'UPDATE building set version = version+1 WHERE buildingUUID=?',
  update_building_version_by_mesh:
      'UPDATE building SET version = version+1 WHERE buildingUUID IN ( SELECT buildingUUID FROM mesh_maddr_info WHERE maddr=? AND meshUUID=?)',
  update_head:
      'UPDATE user_info set head=?,updateTime=? WHERE maddr=?',
  select_building_by_email:
      'SELECT role,buildingUUID FROM building_maddr WHERE maddr = ? and del = 0',
/*  update_building_maddr_by_Id:
      'UPDATE building_maddr a SET a.del = 1 WHERE a.buildingUUID IN (?) ',  */
  update_building_maddr_by_Role0:
      'UPDATE building_maddr a JOIN building b ON a.buildingUUID = b.buildingUUID SET a.del = 1, b.del = 1 WHERE a.buildingUUID in (?); ',
  update_building_by_Id:
      'UPDATE building SET del = 1 WHERE buildingUUID IN (?)',
  update_mesh_del_by_Role0:
      'UPDATE mesh_maddr_info SET del = 1 WHERE buildingUUID IN (?)',
  update_building_maddr_by_Role1:
      'UPDATE building_maddr SET del = 1 WHERE maddr=? AND buildingUUID IN (?)',
  update_mesh_del_by_Role1:
      'UPDATE mesh_maddr_info SET del = 1 WHERE maddr=? AND buildingUUID IN (?)',
  select_receiver_list:
      'SELECT b.name,a.receiver,a.role FROM qr_code as a LEFT JOIN user_info b ON b.maddr=a.receiver WHERE a.maddr = ? and a.buildingUUID = ? and a.receiver IS NOT NULL and a.del = 0 and  b.name IS NOT NULL',
  update_del_share:
      'UPDATE qr_code SET del = 1 ,updateTime = ? WHERE maddr=? AND buildingUUID =? AND receiver = ?',
  del_share:
      'UPDATE qr_code SET del = 1 ,updateTime = ? WHERE receiver=? AND buildingUUID =?',
}

exports.updateMeshInfo = (...params) => alterQuery($sql.update_mesh_info, ...params)

exports.insertMeshMaddrInfo = (...params) => alterQuery($sql.insert_mesh_maddr_info, ...params)

exports.updateFile = (...params) => alterQuery($sql.update_file, ...params)

exports.getMeshInfo = (...params) => selectArrQuery($sql.get_mesh_info, ...params)

exports.mailExist = (...params) => selectQuery($sql.maddr_exist, ...params)

exports.register = (...params) => alterQuery($sql.register_user_info, ...params)

exports.updateCode = (...params) => alterQuery($sql.update_verfiy_code, ...params)

exports.verifyAuth = (...params) => selectQuery($sql.verify_auth, ...params)

exports.updateVerify = (...params) => alterQuery($sql.update_verfiy, ...params)

exports.updateDeviceInfo = (...params) => alterQuery($sql.update_device_info, ...params)

exports.tokenExist = (...params) => selectQuery($sql.token_exist, ...params)

exports.deviceInfo = (...params) => selectArrQuery($sql.device_exist, ...params)

exports.updateBuilding = (...params) => alterQuery($sql.update_building, ...params)

exports.updateBuildingMaddr = (...params) => alterQuery($sql.update_building_maddr, ...params)

exports.insertBuildingMaddr = (...params) => alterQuery($sql.insert_building_maddr, ...params)

exports.selectRole = (...params) => selectQuery($sql.select_role, ...params)

exports.updateImage = (...params) => alterQuery($sql.update_image, ...params)

exports.insertLogProfile = (...params) => alterQuery($sql.insert_log_profile, ...params)

exports.insertLogImage = (...params) => alterQuery($sql.insert_log_image, ...params)

exports.selectQRCodeCount = (...params) => selectQuery($sql.select_qrcode_count, ...params)

exports.shareQRCode = (...params) => alterQuery($sql.insert_share_qrcode, ...params)

exports.selectQRCode = (...params) => selectQuery($sql.select_qrcode, ...params)

exports.updateQRCode = (...params) => alterQuery($sql.update_QR_code, ...params)

exports.insertBuildingMaddrScan = (...params) => alterQuery($sql.insert_building_maddr_scan, ...params)

exports.insertMeshMaddrInfoScan = (...params) => alterQuery($sql.insert_mesh_maddr_info_scan, ...params)

exports.getVersion = (...params) => selectQuery($sql.get_version, ...params)

exports.updateBuildingMaddrDelAll = (...params) => alterQuery($sql.update_building_maddr_del_all, ...params)

exports.updateMeshDelAll = (...params) => alterQuery($sql.update_mesh_del_all, ...params)

exports.updateBuildingAll = (...params) => alterQuery($sql.update_building_all, ...params)

exports.updateBuildingMaddrDel = (...params) => alterQuery($sql.update_building_maddr_del, ...params)

exports.selectRoleMesh = (...params) => selectQuery($sql.select_role_mesh, ...params)

exports.updateMeshDel = (...params) => alterQuery($sql.update_mesh_del, ...params)

exports.updateMesh = (...params) => alterQuery($sql.update_mesh, ...params)

exports.updateUserInfo = (...params) => alterQuery($sql.update_user_info, ...params)

exports.updatePwd = (...params) => alterQuery($sql.update_pwd, ...params)

exports.delUser = (...params) => selectQuery($sql.delete_user, ...params)

exports.selectBuilding = (...params) => selectArrQuery($sql.select_building, ...params)

exports.selectMesh = (...params) => selectArrQuery($sql.select_mesh, ...params)

exports.updateBuildingVersion = (...params) => alterQuery($sql.update_building_version, ...params)

exports.updateBuildingVersionByMesh = (...params) => alterQuery($sql.update_building_version_by_mesh, ...params)

exports.updateHead = (...params) => alterQuery($sql.update_head, ...params)

exports.selectBuildingByEmail = (...params) => selectArrQuery($sql.select_building_by_email, ...params)

exports.updateBuildingMaddrByRole0 = (...params) => alterQuery($sql.update_building_maddr_by_Role0, ...params)

exports.updateBuildingById = (...params) => alterQuery($sql.update_building_by_Id, ...params)

exports.updateNeshDelByRole0 = (...params) => alterQuery($sql.update_mesh_del_by_Role0, ...params)

exports.updateBuildingMaddrByRole1 = (...params) => alterQuery($sql.update_building_maddr_by_Role1, ...params)

exports.updateNeshDelByRole1 = (...params) => alterQuery($sql.update_mesh_del_by_Role1, ...params)

exports.receiverList = (...params) => selectArrQuery($sql.select_receiver_list, ...params)

exports.updateDelShare = (...params) => alterQuery($sql.update_del_share, ...params)

exports.delShare = (...params) => alterQuery($sql.del_share, ...params)